﻿using System;
using System.Collections.Generic;
using System.Linq;
using DBAccessLibrary.Interface;
using DBAccessLibrary.Model;
using DataAccess;
using DBAccessLibrary.Common;
using System.Data;
using System.Windows.Media;


namespace DBAccessLibrary.Grammar
{
    class Db2Grammar : DBGrammar, IGrammar, ILocalGrammar
    {
        public Db2Grammar(ConnectionModel connect)
            : base(connect)
        { }

        private string GetTypeFilter(FieldEntity field, string sql)
        {
            string typeStr = field.TypeName;
            int columnLength;
            int.TryParse(field.Length, out columnLength);
            string length = field.Length;
            string TypeName = field.TypeName.ToUpper();
            string defaultValue = String.Format("'{0}'", field.DefaultValue);
            if (TypeName == "VARCHAR2" || TypeName == "VARCHAR")
            {
                typeStr = "VARCHAR";
                length = String.Format("({0})", field.Length);
            }
            if (TypeName == "INTEGER" || TypeName == "SMALLINT")
            {
                length = "";
                defaultValue = field.DefaultValue;
            }
            if (TypeName == "NUMBER" && columnLength < 9)
            {
                typeStr = "INTEGER";
                length = "";
                defaultValue = field.DefaultValue;
            }
            else if (TypeName == "NUMBER" && columnLength >= 9)
            {
                typeStr = "DECIMAL";
                length = "(18,2)";
                defaultValue = field.DefaultValue;
            }

            if (TypeName == "TIMESTAMP" || TypeName == "DATE")
            {
                length = "";
            }
           
            return string.Format(sql, field.TableName, field.Name, typeStr, length, defaultValue);
        }
        #region IGrammar 成员

        public string AddColumn(FieldEntity field)
        {
            string sql = "ALTER TABLE {0} ADD COLUMN {1} {2}{3}";
            if (!field.IsNull)
            {
                sql += " Not Null";
            }
            if (!string.IsNullOrEmpty(field.DefaultValue))
            {
                sql += " WITH DEFAULT {4}";
            }


            return GetTypeFilter(field, sql);
        }

        public IEnumerable<string> EditColumn(FieldEntity field)
        {
            List<string> sql = new List<string>();
            if (field.IsChange(o => o.Length))
            {
                int length = Convert.ToInt32(field.GetNoChangeValue(o => o.Length).ToString());
                if (Convert.ToInt32(field.Length) > length)
                {
                    sql.Add(GetTypeFilter(field, "ALTER TABLE {0} ALTER COLUMN {1} SET DATA TYPE {2}{3}"));
                }
            }
            if (field.IsChange(o => o.DefaultValue))
            {
                if (!string.IsNullOrEmpty(field.DefaultValue))
                {
                    sql.Add(GetTypeFilter(field, "ALTER TABLE {0} ALTER COLUMN {1} SET WITH DEFAULT {4}"));
                }
                else
                {
                    sql.Add(GetTypeFilter(field, "ALTER TABLE {0} ALTER COLUMN {1} DROP DEFAULT"));
                }
            }

            return sql;
        }

        public IEnumerable<string> RemoveColumn(FieldEntity field)
        {
            List<string> list = new List<string>();
            list.Add(string.Format("ALTER TABLE {0} DROP COLUMN {1}", field.TableName, field.TransName));

            return list;
        }

        public IEnumerable<string> EditPrimary(TableEntity table)
        {
            List<string> sql = new List<string>();
            string pk = table.PrimaryKeyName;
            string[] parimaryKey = table.FileNodes.Where(o => o.IsPrimaryKey).Select(o => o.Name).ToArray();
            if (parimaryKey.Length > 0)
            {
                //删除约束
                if (!string.IsNullOrEmpty(pk))
                {
                    string drop = string.Format("alter table {0} drop constraint {1} ", table.Name, pk);
                    sql.Add(drop);
                }
                else
                {
                    pk = "PK" + parimaryKey[0];
                }

                sql.Add(string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY({2})", table.Name, pk, string.Join(",", parimaryKey)));
            }
            return sql;
        }


         public string SelectTop(TableEntity table, int count)
         {
             return string.Format("SELECT * FROM {0} FETCH FIRST {1} ROWS  ONLY", table.TransName, count);
         }

       
        public string GetChTableSql
        {
            get 
            {
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {

                    return "SELECT TABLENAME,CHINESETABLENAME as CHINATABLENAME,FIELDNAME as COLUMNNAME,CHINESENAME as CHINACOLUMNNAME " +
                        "FROM T_FIELD_DICTIONARY order by TABLENAME";

                }
                return _xml.ChDescription;
            }
        }

        public string IsExistChTableSql
        {
            get 
            {
                string sql = string.Empty;
                if (string.IsNullOrEmpty(_xml.ChDescription))
                {
                    sql = "select name from sysibm.systables where name='T_FIELD_DICTIONARY'";
                }
                return sql;
            }
        }

        public string AppendTabText(string text)
        {
            switch (text.ToUpper().Trim())
            {
                case "EI":
                    return "SELECT COLNAME FROM SYSCAT.COLUMNS WHERE  TABNAME= (SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = {0} AND TABLEID = {1}) AND COLNO = {2}";
                default:
                    return base.TabText(text);;
            }
        }

        public string GetErrorInfo(string errorMsg)
        {
            if (SysCache.DBConnection.ConnectType == DBType.DB2)
            {
                try
                {
                    if (errorMsg.Contains("TBSPACEID=") &&
                          errorMsg.Contains("TABLEID=") &&
                          errorMsg.Contains("COLNO="))
                    {
                        int start = errorMsg.IndexOf("TBSPACEID=", StringComparison.Ordinal) + "TBSPACEID=".Length;
                        string tbspaceid = errorMsg.Substring(start, errorMsg.IndexOf(',', start) - start);
                        start = errorMsg.IndexOf("TABLEID=", StringComparison.Ordinal) + "TABLEID=".Length;
                        string tableid = errorMsg.Substring(start, errorMsg.IndexOf(',', start) - start);
                        start = errorMsg.IndexOf("COLNO=", StringComparison.Ordinal) + "COLNO=".Length;
                        string colno = errorMsg.Substring(start, errorMsg.IndexOf('"', start) - start);


                        string sql = string.Format("SELECT COLNAME FROM SYSCAT.COLUMNS WHERE  TABNAME= (SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = {0} AND TABLEID = {1}) AND COLNO = {2}",
                            tbspaceid, tableid, colno);
                        return sql;
                        //"列" + ExecScalar(sql) + " 参数不正确……";
                    }
                }
                catch { }
            }
            return string.Empty;
        }

        public string GetProcParamInfo(ProcEntity entity)
        {
            if (entity == null)
                return string.Empty;
            string sql;
            if (entity.ChildNodes.Count > 0)
            {
                List<string> list = new List<string>();
                foreach (ProcParamEntity param in entity.ChildNodes)
                {
                    if (param.Status == "IN")
                    {
                        list.Add(string.Format("{0}[{1}({2})]", Environment.NewLine, param.Name, param.TypeName));
                    }
                    else
                    {
                        list.Add(string.Format("{0}[{1}({2})-OUT]", Environment.NewLine, param.Name, param.TypeName));
                    }
                }
                sql = String.Format("({0})", string.Join(",", list.ToArray()));
            }
            else
            {
                sql = "()";
            }
            return string.Format("{0} {1}{2}",
                                 "CALL",
                                 entity.Name,
                                 sql);
        }



        protected override string GetProcName(string sql, out string paramStr)
        {
            paramStr = string.Empty;
            if (sql.StartsWith("CALL ", StringComparison.CurrentCultureIgnoreCase))
            {
                int start = sql.IndexOf("CALL ", StringComparison.CurrentCultureIgnoreCase) + 5;
                int end = sql.IndexOf("(", System.StringComparison.Ordinal);
                paramStr = sql.Substring(end + 1, sql.LastIndexOf(")", System.StringComparison.Ordinal) - end - 1);
                return sql.Substring(start, end - start);
            }
            return string.Empty;
        }

        public string GetPageingSql(string sql, decimal index, decimal count)
        {
            string newSql = String.Format("SELECT tab.* FROM (SELECT {{0}} ROWNUMBER,t.* FROM ({0}{{1}}{0}) t) tab  WHERE tab.ROWNUMBER >= {1} and tab.ROWNUMBER < {2}", Environment.NewLine, index, count);

            newSql = string.Format(newSql, "ROW_NUMBER() OVER()", sql);

            return newSql;
        }

      
        protected override string GetRowNumUpdate(string tableName, string where, string column, string rownumber)
        {

            return string.Format("UPDATE ({0}) T1 SET {1} WHERE T1.ROWNUMBER={2}",
                                String.Format("{0} {1}", string.Format("SELECT ROW_NUMBER() OVER() ROWNUMBER,{0}.* FROM {0}", tableName), where),
                                column,
                                rownumber);

        }
        protected override string GetRowNumDelete(string tableName, string where, string rownumber)
        {
            return string.Format("DELETE FROM ({0}) T1 WHERE T1.ROWNUMBER={1}",
                                String.Format("{0} {1}", string.Format("SELECT ROW_NUMBER() OVER() ROWNUMBER,{0}.* FROM {0}", tableName), where),
                                rownumber);

        }
        #endregion

        #region ILocalGrammar 成员

        public DatabaseType GetConnectionStr(out string connectStr)
        {
            connectStr = "Database={0};user id={1};password={2};server={3}";


            connectStr = string.Format(connectStr,
                                             _connect.DataBase,
                                             _connect.UserID,
                                             _connect.PassWord,
                                             _connect.Server);

            return DatabaseType.DB2;

        }

        #endregion

        public override void SaveChange(TableEntity table, IList<FieldEntity> newList, Action<string, Color> WriteLog,Func<string,bool> showMsg)
        {
            List<FieldEntity> removeList = new List<FieldEntity>();
            List<FieldEntity> addList = new List<FieldEntity>();
            bool isEditTable = false;
            bool isPrimary = false;
            try
            {
                foreach (FieldEntity entity in newList)
                {
                    //删除
                    if (entity.Status == EditStatus.Delete)
                    {
                        foreach (string str in entity.RemoveColumnSql)
                        {
                            WriteLog(str, Colors.Black);
                            DBOperatorFactory.GetOperator.ExecNonQuery(str, SysCache.DBConnection, null, TransactionType.Transaction);
                        }
                        removeList.Add(entity);
                    }
                    //新增
                    else if (entity.Status == EditStatus.Add)
                    {
                        string sql = entity.AddColumnSql;
                        WriteLog(sql, Colors.Black);
                        DBOperatorFactory.GetOperator.ExecNonQuery(sql, SysCache.DBConnection, null, TransactionType.Transaction);
                        addList.Add(entity);
                    }
                    //编辑
                    else if (entity.Status == EditStatus.Modify && !isEditTable)
                    {
                        if (entity.IsChange(o => o.Length) || entity.IsChange(o => o.DefaultValue))
                        {
                            IEnumerable<string> editStr = DBOperatorFactory.GetGrammar(SysCache.DBConnection).EditColumn(entity);
                            if (editStr.Count() == 0)
                            { isEditTable = true; }
                            else
                            {
                                foreach (string str in editStr)
                                {
                                    WriteLog(str, Colors.Black);
                                    DBOperatorFactory.GetOperator.ExecNonQuery(str, SysCache.DBConnection, null, TransactionType.Transaction);
                                }
                            }
                        }
                        else
                        {
                            isEditTable = true;
                        }
                    }

                    if (!isPrimary && entity.IsChange(o => o.IsPrimaryKey))
                    {
                        isPrimary = true;
                    }

                    if (entity.Status != EditStatus.UnChange)
                    {
                        //更新字典描述表
                        DBOperatorFactory.GetOperator.SaveDescription(table, entity, string.Empty, false, TransactionType.Transaction);
                    }
                }

                //主键
                if (isPrimary)
                {
                    foreach (string str in table.AddPrimarySql)
                    {
                        WriteLog(str, Colors.Black);
                        DBOperatorFactory.GetOperator.ExecNonQuery(str, SysCache.DBConnection, null, TransactionType.Transaction);
                    }
                }
                //删除表重建
                if (isEditTable)
                {
                    if (showMsg != null)
                    {
                        if (!showMsg("该操作会造成重建表结构，是否继续?"))
                        {
                            throw new Exception("取消操作");
                        }
                    }
                    List<DBParam> list = new List<DBParam>();
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    list.Add(new DBParam { Name = "EXEC_MODE", ParamType = 1, TypeName = "VARCHAR", Value = "APPLY_CONTINUE_ON_ERROR" });
                    list.Add(new DBParam { Name = "SQL_STMT", ParamType = 1, TypeName = "VARCHAR", Value = GetCreateTable(SysCache.DBConnection.ConnectType, table,false) + " IN USERSPACE1" });
                    list.Add(new DBParam { Name = "ALITER_ID", ParamType = 1, TypeName = "INTEGER", Value = -1 });
                    list.Add(new DBParam { Name = "MSG", ParamType = 2, TypeName = "VARCHAR", Value = "?" });
                    WriteLog("调用存储过程-->ALTOBJ", Colors.Black);
                    DBOperatorFactory.GetOperator.ExecProc("ALTOBJ", list, out dic, SysCache.DBConnection, TransactionType.Transaction);
                    
                    WriteLog("返回-->" + dic["MSG"], Colors.Black);
                    DataSet ds = DBOperatorFactory.GetOperator.ExecSqlToDS(dic["MSG"].ToString());
                    DataRow row = ds.Tables[0].AsEnumerable().FirstOrDefault(o => o.Field<string>("OBJ_NAME") != table.Name && o.Field<string>("OBJ_NAME").EndsWith("_EXCEPTION"));

                    string tableName = row["OBJ_NAME"].ToString();
                    WriteLog("删除表-->" + tableName, Colors.Black);
                    DBOperatorFactory.GetOperator.ExecNonQuery("DROP TABLE " + tableName,SysCache.DBConnection,null, TransactionType.Transaction);
                    int index = tableName.LastIndexOf("_EXCEPTION");

                    tableName = tableName.Remove(index, tableName.Length - index);
                    WriteLog("删除表-->" + tableName, Colors.Black);
                    DBOperatorFactory.GetOperator.ExecNonQuery("DROP TABLE " + tableName, SysCache.DBConnection, null, TransactionType.Transaction);
                }
                DBOperatorFactory.GetOperator.CommitTransaction(SysCache.DBConnection);
                table.Commit();
                //删除
                removeList.ForEach(o =>
                {
                    newList.Remove(o);
                    table.Remove(o);
                });
                //新增
                addList.ForEach(o =>{
                    o.Status = EditStatus.UnChange;
                    table.Add(o);
                });
                foreach (FieldEntity entity in removeList)
                {
                    newList.Remove(entity);
                }
                WriteLog("提交成功", Colors.Blue);
            }
            catch (Exception ex)
            {
                DBOperatorFactory.GetOperator.RollbackTransaction(SysCache.DBConnection);
                table.RollBack();
                if (isEditTable)
                {
                    WriteLog("基础数据回滚成功，但存储过程无法回滚，可能会产生2张临时表，请手动删除。异常详细:" + ex.Message, Colors.Red); 
                }
                else
                {
                    WriteLog("回滚成功.异常详细:" + ex.Message, Colors.Red);
                }
                throw;
            }
        }

    }
}
